Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Viewing Triggers

When triggers are added to tables, the definitions are added to the data dictionary. The constraint information is kept in internal tables in the data dictionary. These tables can be queried from several views. The views defined for constraint information are as follows:


View Which Triggers?

ALL_TRIGGERS Triggers defined on objects accessible by the user.
USER_TRIGGERS Triggers defined on objects owned by the user.
DBA_TRIGGERS Triggers defined on all objects in the system.

In these tables, some of the important information concerning triggers is contained in the following columns:

  TRIGGER_NAME: The identifier of the trigger.
  TRIGGERING_EVENT: What event “fires” the trigger.
  TABLE_NAME: The name of the table that the trigger is set on.
  WHEN_CLAUSE: When the trigger fires.
  TRIGGER_BODY: The code that is run when the trigger fires.

This information can be useful when analyzing or debugging triggers.

Review of Triggers

Triggers can be very useful not only as a supplement to Oracle integrity constraints but as a way to provide for additional auditing, data verification, and data generation. By using triggers for these purposes, you can simplify and enhance your application code. By reducing the number of SQL statements that must be called by the application and allowing internal functions to provide the features, you improve efficiency.

Triggers are frequently used to enforce business rules that cannot be controlled by Oracle integrity constraints and as supplements to the Oracle auditing facilities. Triggers can also be used as an efficient way of logging access to specific tables. Because triggers use significant CPU resources, you should use them sparingly—but take advantage of them if you need them.

Audit Trails

Oracle has built-in auditing facilities you can use to create an audit trail of events that have occurred in the RDBMS. The audit trail is somewhat configurable and can contain as much or as little auditing information as you want. By increasing the number of events to be audited, you increase the overhead incurred by the RDBMS. It is wise to audit as little as you can get away with to reduce the impact on the system.

Audit trail information can contain the following:

  The user accessing the object
  The name of the object accessed
  The operation performed or attempted
  The timestamp of that operation
  The session and terminal identifiers

Use the AUDIT and NOAUDIT commands to turn auditing on and off on a per-object basis. The procedure for doing this, and the objects that can be modified, are explained in the Oracle documentation and are not covered here.

Auditing is an important part of the security of your system, but you should limit it to only the most critical objects. Auditing involves significant use of system resources; by reducing the amount of auditing you do, you can lessen the impact on the system.

If you are in a somewhat secure environment, you may not find auditing necessary. But if you are in a large organization with lots of activity on the RDBMS, you should seriously consider auditing. What to audit is a decision to be made on a case-by-case basis and cannot be taken lightly. As much as possible, limit your auditing to reduce overhead, but audit as much as you need to.

If auditing is being done, the auditing information is kept in the AUD$ table. Check this table periodically to make sure that it does not fill up the SYSTEM tablespace.

Serial Reads

If your application requires serializable reads, you must change the parameters SERIALIZABLE and ROW_LOCKING in the Oracle initialization file. By changing these parameters, you alter the performance of the system dramatically. Oracle recommends that users implement applications using the default row locking.

Oracle has put considerable effort into improving the performance of serializable transactions to compete with other RDBMS vendors in the TPC-C benchmark and has done very well. This does not mean that application developers are forced to use serializable transactions. Oracle row-level locking is suitable for almost every application.

By setting the Oracle initialization parameter SERIALIZABLE to TRUE, queries acquire table-level read locks, preventing updates to the objects being read until the transaction containing the query has been committed. This mode provides for repeatable reads and ensures that two queries for the same data in the same transaction see the same data.

This parameter provides for ANSI-degree-three consistency but at a considerable cost in concurrency.

Summary

This chapter presented several ways you can optimally provide for data integrity. When designing applications, you should take advantage of Oracle features such as integrity constraints and triggers where applicable. By using internal features such as integrity constraints, you can reduce unnecessary overhead in the application, the network, and the server.

It is important that you do not sacrifice integrity for performance. A database that does not guarantee data integrity is not worth having. The database and the application are only as good as the data stored within them.

Another topic covered in this chapter was the use of auditing. Although auditing may or may not be an important part of your operation, by taking advantage of only the auditing features you need and eliminating those you don’t need, you can optimize performance and still track required information.

Finally, the chapter briefly discussed the use of serializable reads, which some applications need for data integrity. I believe that a well-designed application that understands Oracle row locking can perform well and not sacrifice data integrity. The use of serializable transactions does not provide more data integrity, it just provides it in a different way than row locking. If at all possible, avoid the use of serializable reads in your database and application.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.